package com.mi.rule.db.succinct.test;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.mi.rule.db.core.MiPage;
import com.mi.rule.db.core.MiWrapper;
import com.mi.rule.db.dao.MiDao;
import com.mi.rule.db.succinct.entity.Demo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.util.*;

/**
 * 使用方式 一
 * 通过MiDao操作数据库
 * 增 删 改 查 统计 分页 多表联查
 */
@Slf4j
@RestController
@RequestMapping("/template1")
public class Template_1 {
    @Autowired
    private MiDao miDao2;

    /**
     * 增
     */
    @RequestMapping("/insert")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> insertOne(@RequestBody Map<Object, Object> map) {
        //初始化数据
        miDao2.delete("delete from 0_demo where id != 0");

        //insert
        Demo demo1 = new Demo();
        demo1.setName("1");
        long insert = miDao2.insert(demo1);

        //insert
        Demo demo2 = new Demo();
        demo2.setId("2");
        demo2.setName("2");
        long insert1 = miDao2.insert(demo2);

        //insertOrUpdate
        Demo demo3 = new Demo();
        demo3.setId("3");
        demo3.setName("不存在--执行插入操作3");
        long l = miDao2.insertOrUpdate(demo3);

        //insertOrUpdate
        Demo demo4 = new Demo();
        demo4.setId("2");
        demo4.setName("存在--执行修改操作2");
        long l1 = miDao2.insertOrUpdate(demo4);


        //insertOrUpdate
        Demo demo5 = new Demo();
        demo5.setId("9");
        demo5.setName("把ID=1改成9");
        MiWrapper<Demo> demoMiWrapper = new MiWrapper<>(Demo.class);
        demoMiWrapper.eq(Demo.ID, 1);
        long l6 = miDao2.insertOrUpdate(demo5, demoMiWrapper);


        //insertBatch
        List<Demo> list1 = new ArrayList<>();
        for (int i = 10; i < 16; i++) {
            Demo add = new Demo();
            add.setName(i + "");
            add.setCreateTime(LocalDateTime.now());
            list1.add(add);
        }
        long l2 = miDao2.insertBatch(list1);

        //insertBatch
        List<Demo> arrayList2 = new ArrayList<>();
        for (int i = 20; i < 26; i++) {
            Demo add = new Demo();
            add.setId(i + "");
            add.setName(i + "");
            add.setCreateTime(LocalDateTime.now());
            arrayList2.add(add);
        }
        long l3 = miDao2.insertBatch(arrayList2);


        //insertOrUpdateBatch
        List<Demo> list3 = new ArrayList<>();
        for (int i = 30; i < 36; i++) {
            Demo add = new Demo();
            add.setId(i + "");
            add.setName(i + "");
            add.setCreateTime(LocalDateTime.now());
            list3.add(add);
        }

        Demo add1 = new Demo();
        add1.setId("20");
        add1.setName("insertOrUpdateBatch -修改成功20");
        Demo add2 = new Demo();
        add2.setId("21");
        add2.setName("insertOrUpdateBatch -修改成功21");
        list3.add(add1);
        list3.add(add2);
        long l4 = miDao2.insertOrUpdateBatch(list3);

        //操作完成
        log.info("end");
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 删
     */
    @RequestMapping("/delete")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> delete(@RequestBody Map<Object, Object> map) {
        //初始化数据
        initData();

        //deleteById
        long l = miDao2.deleteById(Demo.class, 1);

        //deleteByIds
        long l1 = miDao2.deleteByIds(Demo.class, Arrays.asList(2, 3, 4));

        //条件构造器
        MiWrapper<Demo> wrapper = new MiWrapper<>(Demo.class);
        wrapper.eq(Demo.ID, 10);
        wrapper.or();
        wrapper.in(Demo.ID, Arrays.asList(5, 6, 7, 8, 9));
        long delete = miDao2.delete(wrapper);

        //deleteByMap
        Map<String, Object> map1 = new HashMap<>();
        map1.put("id", "20");
        long l2 = miDao2.deleteByMap(Demo.class, map1);

        map.put("操作完成", "true");
        return map;
    }

    /**
     * 改
     */
    @RequestMapping("/update")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> update(@RequestBody Map<Object, Object> map) {
        //初始化数据
        initData();

        //update
        MiWrapper<Demo> wrapper = new MiWrapper<>(Demo.class);
        wrapper.sqlSet("name", "修改了1=10");
        wrapper.sqlSet("id=10");
        wrapper.eq(Demo.ID, 10);
        long update = miDao2.update(wrapper);


        //update
        Demo demo2 = new Demo();
        demo2.setName("修改了id=20");
        MiWrapper<Demo> wrapper2 = new MiWrapper<>(Demo.class);
        wrapper2.eq(Demo.ID, 20);
        long update1 = miDao2.update(demo2, wrapper2);


        //updateById
        Demo demo3 = new Demo();
        demo3.setId("30");
        demo3.setName("修改了ID=30");
        long l = miDao2.updateById(demo3);


        //updateBatchById
        Demo demo40 = new Demo();
        demo40.setId("40");
        demo40.setName("修改了ID=40");
        Demo demo50 = new Demo();
        demo50.setId("50");
        demo50.setName("修改了ID=50");

        List<Demo> list = new ArrayList<>();
        list.add(demo40);
        list.add(demo50);
        long l1 = miDao2.updateBatchById(list);

        map.put("操作完成", "true");
        return map;
    }

    /**
     * 查
     */
    @RequestMapping("/select")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> select(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();

        //selectOne
        MiWrapper<Demo> wrapper1 = new MiWrapper<>(Demo.class);
        wrapper1.in(Demo.ID, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
        wrapper1.or();
        wrapper1.in(Demo.ID, Arrays.asList(20, 21, 22, 23));
        Demo demo10 = miDao2.selectOne(wrapper1);


        //selectById
        Demo demo20 = miDao2.selectById(Demo.class, 20);


        //selectMap
        Map<String, Object> map1 = miDao2.selectMap(wrapper1);


        //list
        List<Demo> list = miDao2.list(Demo.class);
        List<Demo> list1 = miDao2.list(wrapper1);


        //listMaps
        List<Map<String, Object>> maps = miDao2.listMaps(Demo.class);
        List<Map<String, Object>> maps1 = miDao2.listMaps(wrapper1);


        //listObjs
        List<Object> objects = miDao2.listObjs(Demo.class);


        //listObjs-只查ID列
        wrapper1.select("id");
        List<Object> idList = miDao2.listObjs(wrapper1);
        System.out.println(idList);

        //listByIds
        List<Demo> list2 = miDao2.listByIds(Demo.class, Arrays.asList(1, 2, 3));


        //listByMap
        HashMap<String, Object> map2 = new HashMap<>();
        map2.put("id", 30);
        List<Demo> list3 = miDao2.listByMap(Demo.class, map2);


        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 统计
     */
    @RequestMapping("/count")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> count(@RequestBody Map<Object, Object> map) {
        //初始化数据
        initData();

        //统计--count
        long count = miDao2.count(Demo.class);

        //统计--count
        MiWrapper<Demo> wrapper = new MiWrapper<>(Demo.class);
        wrapper.eq(Demo.ID, 10);
        long count2 = miDao2.count(wrapper);


        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 分页
     */
    @RequestMapping("/page")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> page(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();


        //无条件分页
        IPage<Demo> page1 = miDao2.page(new MiPage<>(1, 20), Demo.class);

        //无条件分页
        IPage<Map<String, Object>> mapIPage1 = miDao2.pageMaps(new MiPage<>(1, 20), Demo.class);

        //        条件
        MiWrapper<Demo> wrapper = new MiWrapper<>(Demo.class);
        wrapper.eq("id", 1);

        //有条件分页
        IPage<Demo> page = miDao2.page(new MiPage<>(1, 10), wrapper);

        //有条件分页
        IPage<Map<String, Object>> mapIPage = miDao2.pageMaps(new MiPage<>(1, 10), wrapper);


        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

    /**
     * 多表联查
     * 不用构造器也可以纯手写字符串-道理是一样的
     */
    @RequestMapping("/join")
    @Transactional(rollbackFor = Exception.class)
    public Map<Object, Object> join(@RequestBody Map<Object, Object> map) {
        //==============初始化数据==================
        initData();

        //  主表0_demo 联查三张表
        //
        //1.联查0_file表name字段 as fileName
        //2.联查0_user表name字段 as userName
        //3.联查0_type表name字段 as typeName
        MiWrapper<Demo> wrapper = new MiWrapper<>(Demo.class);
        wrapper.setTableName("0_demo de");
        wrapper.select("de.*", "file.name as fileName", "user.name as userName", "type.name as typeName");
        wrapper.leftJoin(" 0_file file ", " file.id = de.file_id ");
        wrapper.leftJoin(" 0_user user ", " user.id = de.user_id ");
        wrapper.leftJoin(" 0_type type ", " type.id = de.type_id ");
        //查询条件
        wrapper.in("de.id", 1, 2, 3, 4, 5, 6, 7, 8, 9);
        wrapper.ne("de.id", 100);// id !=100
        wrapper.ne("de.name", 100);// name !='100'

        //查询结果
        Demo demo = miDao2.selectOne(wrapper);
        List<Demo> list = miDao2.list(wrapper);
        List<Map<String, Object>> maps = miDao2.listMaps(wrapper);
        List<Object> objects = miDao2.listObjs(wrapper);
        IPage<Demo> page = miDao2.page(new MiPage<>(1, 10), wrapper);
        IPage<Demo> page1 = miDao2.page(new MiPage<>(1, 10), wrapper);

        map = new HashMap<>();
        map.put("操作完成", "true");
        return map;
    }

//
//        //左联
//        MiWrapper<Demo> wrapper_left = getMiWrapper(1);
//        String wrapper_left_sql_string =
//                "select de.* ,file.name as fileName ,user.name as userName ,type.name as typeName" +
//                        " from 0_demo de " +
//                        " left join 0_file file on file.id = de.id " +
//                        " left join 0_user user on user.id = de.id " +
//                        " left join 0_type type on type.id = de.id " +
//                        " where  de.id in (1,2,3,4,5,6,7,8)  and de.id !=999 and de.name !='999'";
//
//        //获取右联-条件构造器
//        MiWrapper<Demo> wrapper_right = getMiWrapper(2);
//        String wrapper_right_sql_string =
//                "select de.* ,file.name as fileName ,user.name as userName ,type.name as typeName" +
//                        " from 0_demo de " +
//                        " right join 0_file file on file.id = de.id " +
//                        " right join 0_user user on user.id = de.id " +
//                        " right join 0_type type on type.id = de.id " +
//                        " where de.id in (1,2,3,4,5,6,7,8)  and de.id !=999 and de.name !='999'";
//
//        //获取全联-条件构造器
//        MiWrapper<Demo> wrapper_join = getMiWrapper(3);
//        String wrapper_join_sql_string =
//                "select de.* ,file.name as fileName ,user.name as userName ,type.name as typeName" +
//                        " from 0_demo de " +
//                        " join 0_file file on file.id = de.id " +
//                        " join 0_user user on user.id = de.id " +
//                        " join 0_type type on type.id = de.id " +
//                        " where de.id in (1,2,3,4,5,6,7,8)  and de.id !=999 and de.name !='999'";
//
//
//        //联查统计
//        long count1 = miDao2.count(wrapper_right);
//        long count2 = miDao2.count(wrapper_join);
//
//
//        //联查一条数据
//        Demo demo1 = miDao2.selectOne(wrapper_right);
//        Demo demo2 = miDao2.selectOne(wrapper_join);
//        Map map2 = miDao2.selectOne(wrapper_right_sql_string);
//        Map map3 = miDao2.selectOne(wrapper_join_sql_string);


    //联查一条集合数据
//        List<Demo> demos1 = miDao2.selectList(wrapper_right);
//        List<Demo> demos2 = miDao2.selectList(wrapper_join);
//        List<Map<?, ?>> maps1 = miDao2.selectList(wrapper_right_sql_string);
//        List<Map<?, ?>> maps2 = miDao2.selectList(wrapper_join_sql_string);


    //联查分页数据
//        MiPage<Demo> page1 = miDao2.page(new MiPage<>(1, 10), wrapper_right);
//        MiPage<Demo> page2 = miDao2.page(new MiPage<>(1, 10), wrapper_join);
//        MiPage<Map<?, ?>> page4 = miDao2.page(new MiPage<>(1, 10), wrapper_right_sql_string);
//        MiPage<Map<?, ?>> page5 = miDao2.page(new MiPage<>(1, 10), wrapper_join_sql_string);


//    private MiWrapper<Demo> getMiWrapper(int type) {
//        MiWrapper<Demo> wrapper = new MiWrapper<>(Demo.class);
//        wrapper.setTableName("0_demo de");
//        wrapper.select("de.*", "file.name as fileName", "user.name as userName", "type.name as typeName");
//
//        //        构造左联条件
//        if (type == 1) {
//            wrapper.leftJoin("0_file file", "file.id = de.id");
//            wrapper.leftJoin("0_user user", "user.id = de.id");
//            wrapper.leftJoin("0_type type", "type.id = de.id");
//        }
//
//        //        构造右联条件
//        if (type == 2) {
//            wrapper.rightJoin("0_file file", "file.id = de.id");
//            wrapper.rightJoin("0_user user", "user.id = de.id");
//            wrapper.rightJoin("0_type type", "type.id = de.id");
//        }
//
//        //        构造全联条件
//        if (type == 3) {
//            wrapper.join("0_file file", "file.id = de.id");
//            wrapper.join("0_user user", "user.id = de.id");
//            wrapper.join("0_type type", "type.id = de.id");
//        }
//
//        //查询条件
//        wrapper.in("de.id", 1, 2, 3, 4, 5, 6, 7, 8, 9); //id in （）
//        wrapper.ne("de.id", 999);// id !=999
//        wrapper.ne("de.name", 999);// name !='999'
//
//        return wrapper;
//    }


    private void initData() {
        log.info("start");
        long delete = miDao2.delete("delete from 0_demo where id != 0");
        List<Demo> arrayList1 = new ArrayList<>();
        for (int i = 1; i < 300; i++) {
            Demo demo = new Demo();
            demo.setId(i + "");
            demo.setName(i + "");
            demo.setCreateTime(LocalDateTime.now());
            arrayList1.add(demo);
        }
        long l = miDao2.insertBatch(arrayList1, 100);
    }

}
